﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using web.data.entities;
using System.Configuration;
using Webdiyer.WebControls.Mvc;

namespace web.bussines
{
    public class h_content_service
    {
        //Bat dau content_service thuy lam 
        /// <summary>
        /// list all school
        /// </summary>
        /// <param name="pageindex"></param>
        /// <param name="pagesite"></param>
        /// <param name="text_search"></param>
        /// <returns></returns>
        public PagedList<school> getSchoolBuilder(int pageindex, int pagesite, string text_search)
        {
            using (var data = new baseEntities())
            {

                var list_school = (from p in data.schools
                                   select p);

                if (!string.IsNullOrEmpty(text_search))
                {
                    list_school = list_school.Where(p => p.school_name.Contains(text_search));
                }

                list_school = list_school.OrderBy(p => p.school_id);

                return list_school.ToPagedList(pageindex, pagesite);
            }
        }



        public school getSchoolByID(int id)
        {
            using (var data = new baseEntities())
            {
                var type = data.schools.Where(p => p.school_id == id).FirstOrDefault();

                return type;
            }
        }



        public void editSchool(school school)
        {
            using (var data = new baseEntities())
            {
                var edit_school = data.schools.Where(p => p.school_id == school.school_id).SingleOrDefault();
                edit_school.school_name = school.school_name;
                edit_school.school_address = school.school_address;
                edit_school.descriptions = school.descriptions;
                //edit_school.type_school_id = school.type_school_id;
                data.SaveChanges();
            }

        }



        public int createSchool(school school)
        {
            using (var data = new baseEntities())
            {
                data.AddToschools(school);
                data.SaveChanges();

                return school.school_id;
            }
        }

        /// <summary>
        /// departments
        /// </summary>
        /// <param name="pageindex"></param>
        /// <param name="pagesite"></param>
        /// <param name="text_search"></param>
        /// <returns></returns>
        public PagedList<departments> getdepartmentsBuilder(int pageindex, int pagesite, string text_search)
        {
            using (var data = new baseEntities())
            {

                var list_departments = (from p in data.departments
                                        select p);

                if (!string.IsNullOrEmpty(text_search))
                {
                    list_departments = list_departments.Where(p => p.departments_name.Contains(text_search));
                }

                list_departments = list_departments.OrderBy(p => p.departments_id);

                return list_departments.ToPagedList(pageindex, pagesite);
            }
        }


        public PagedList<employee_family> getEmployeeFamilyBuilder(int pageindex, int pagesite, string text_search, string hometown, string current_address, string job, string birth_day,ref int total_record )
        {
            using (var data = new baseEntities())
            {

                var list_employee_family = (from p in data.employee_family where p.family_status == 1
                                        select p);

                if (!string.IsNullOrEmpty(text_search))
                {
                    list_employee_family = list_employee_family.Where(p => p.fullname.Contains(text_search));
                }
                if (!string.IsNullOrEmpty(hometown))
                {
                    list_employee_family = list_employee_family.Where(p => p.hometown.Contains(hometown));
                }
                if (!string.IsNullOrEmpty(current_address))
                {
                    list_employee_family = list_employee_family.Where(p => p.current_address.Contains(current_address));
                }
                if (!string.IsNullOrEmpty(job))
                {
                    list_employee_family = list_employee_family.Where(p => p.job.Contains(job));
                }
                if (!string.IsNullOrEmpty(birth_day))
                {
                    list_employee_family = list_employee_family.Where(p => p.birth_day.Contains(birth_day));
                }

                list_employee_family = list_employee_family.OrderBy(p => p.family_id);
                var result = (from e in list_employee_family
                              select e);

                total_record = result.ToList().Count;

                //return result.ToPagedList(pageindex, pagesite); 
                return result.ToPagedList(pageindex, pagesite);
            }
        }


        //public void updateStatusDepartment(departments department)
        //{
        //    using (var data = new baseEntities())
        //    {
        //        var department_change_status = data.departments.Where(p => p.departments_id == department.departments_id).SingleOrDefault();
        //        department_change_status.departments_status = department.departments_status;
        //        data.SaveChanges();
        //    }
        //}

        //public departments getDepartmentByID(int id)
        //{
        //    using (var data = new baseEntities())
        //    {
        //        var department = data.departments.Where(p => p.departments_id == id).FirstOrDefault();

        //        return department;
        //    }
        //}

        //public void editDepartment(departments department)
        //{
        //    using (var data = new baseEntities())
        //    {
        //        var edit_department = data.departments.Where(p => p.departments_id == department.departments_id).SingleOrDefault();
        //        edit_department.departments_id = department.departments_id;
        //        edit_department.departments_name = department.departments_name;
        //        edit_department.descriptions = department.descriptions;
        //        //edit_department.branch_id = department.branch_id;
        //        edit_department.departments_status = department.departments_status;
        //        data.SaveChanges();
        //    }
        //}

        //public int createDepartment(departments department)
        //{
        //    using (var data = new baseEntities())
        //    {
        //        data.AddTodepartments(department);
        //        data.SaveChanges();

        //        return department.departments_id;
        //    }
        //}




        public object_employee getobject_employeeByID(int id)
        {
            using (var data = new baseEntities())
            {
                var object_employee = data.object_employee.Where(p => p.object_employee_id == id).FirstOrDefault();
                return object_employee;
            }
        }

        public List<object_employee> getListobject_employeeByParent(int? id)
        {
            using (var data = new baseEntities())
            {
                var list_object_employee = data.object_employee.Where(p => p.object_employee_name != null);
                if (id != null)
                {
                    list_object_employee = list_object_employee.Where(p => p.parent_object == id);
                }
                else
                {
                    list_object_employee = list_object_employee.Where(p => p.parent_object == null);
                }

                return list_object_employee.ToList();
            }
        }

        public PagedList<object_employee> getobject_employeeBuilder(int pageindex, int pagesite, string text_search, int? parent)
        {
            using (var data = new baseEntities())
            {
                IQueryable<object_employee> list = getListobject_employeeBuilder(parent).AsQueryable<object_employee>();

                if (!string.IsNullOrEmpty(text_search))
                {
                    list = list.Where(p => p.object_employee_name.Contains(text_search) || p.object_desc.Contains(text_search));
                }

                list = list.OrderBy(p => p.object_employee_id);

                return list.ToPagedList(pageindex, pagesite);
            }
        }

        public List<object_employee> getListobject_employeeBuilder(int? id)
        {
            List<object_employee> sb = new List<object_employee>();
            return getListobject_employeeBuilder(ref sb, id, true);
        }

        public List<object_employee> getListobject_employeeBuilder(ref List<object_employee> sb, int? id, bool root)
        {
            List<object_employee> list_parent = getListobject_employeeByParent(id);
            foreach (var item in list_parent)
            {
                if (!root)
                {
                    item.object_employee_name = getobject_employeeByID((int)item.parent_object).object_employee_name + ">>" + item.object_employee_name;
                    object_employee type_emp = getobject_employeeByID(id ?? 0);
                    foreach (var itemStr in sb)
                    {
                        if (itemStr.object_employee_id == type_emp.object_employee_id)
                        {
                            if (itemStr.object_employee_name.Contains(">>"))
                            {
                                int laststr = itemStr.object_employee_name.LastIndexOf(">>") + 2;
                                string addStr = itemStr.object_employee_name.Substring(0, laststr);
                                item.object_employee_name = addStr + item.object_employee_name;
                            }
                        }
                    }
                }
                sb.Add(item);
                List<object_employee> list_child = getListobject_employeeByParent(item.object_employee_id);
                if (list_child.Count() > 0)
                {
                    getListobject_employeeBuilder(ref sb, item.object_employee_id, false);
                }
            }
            return sb;
        }

        public object_employee getObjectEmployeeByID(int id)
        {
            using (var data = new baseEntities())
            {
                var object_employee = data.object_employee.Where(p => p.object_employee_id == id).FirstOrDefault();

                return object_employee;
            }
        }

        public void editObjectEmployee(object_employee object_employee)
        {
            using (var data = new baseEntities())
            {
                var edit_object_employee = data.object_employee.Where(p => p.object_employee_id == object_employee.object_employee_id).SingleOrDefault();
                edit_object_employee.object_employee_name = object_employee.object_employee_name;
                edit_object_employee.object_desc = object_employee.object_desc;
                edit_object_employee.parent_object = object_employee.parent_object;
                data.SaveChanges();
            }
        }
        public int createObjectEmployee(object_employee object_employee)
        {
            using (var data = new baseEntities())
            {
                data.AddToobject_employee(object_employee);
                data.SaveChanges();

                return object_employee.object_employee_id;
            }
        }

        public List<object_employee> listObjectEmployee()
        {
            using (var data = new baseEntities())
            {
                var object_employee = data.object_employee.ToList();
                return object_employee;
            }
        }
        /// <summary>
        /// employee_foreign_countries
        /// </summary>
        /// <param name="pageindex"></param>
        /// <param name="pagesite"></param>
        /// <param name="text_search"></param>
        /// <returns></returns>
        public PagedList<employee_foreign_countries> getemployee_foreign_countriesBuilder(int pageindex, int pagesite, int? employeeId, string text_search)
        {
            using (var data = new baseEntities())
            {

                var list_employee_foreign_countries = (from p in data.employee_foreign_countries
                                                       where p.employee_id == employeeId
                                                       select p);

                if (!string.IsNullOrEmpty(text_search))
                {
                    list_employee_foreign_countries = list_employee_foreign_countries.Where(p => p.country.Contains(text_search));
                }

                list_employee_foreign_countries = list_employee_foreign_countries.OrderBy(p => p.foreign_countries);

                return list_employee_foreign_countries.ToPagedList(pageindex, pagesite);
            }
        }

        public employee getemployeeByID(int id)
        {
            using (var data = new baseEntities())
            {
                var type = data.employees.Where(p => p.employee_id == id).FirstOrDefault();

                return type;
            }
        }

        public List<employee> listemployee()
        {
            using (var data = new baseEntities())
            {
                var employee_ = data.employees.ToList();
                return employee_;
            }
        }

        public List<employee> getListemployee()
        {
            using (var data = new baseEntities())
            {
                var list_employee = data.employees.OrderBy(p => p.employee_id).ToList();
                return list_employee;
            }
        }


        public employee_foreign_countries getEmployeeForeignCountriesByID(int id)
        {
            using (var data = new baseEntities())
            {
                var employee_foreign_country = data.employee_foreign_countries.Where(p => p.foreign_countries == id).FirstOrDefault();

                return employee_foreign_country;
            }
        }

        public void editEmployeeForeignCountries(employee_foreign_countries employee_foreign_country)
        {
            using (var data = new baseEntities())
            {
                var edit_employee_foreign_country = data.employee_foreign_countries.Where(p => p.foreign_countries == employee_foreign_country.foreign_countries).SingleOrDefault();
                edit_employee_foreign_country.foreign_countries = employee_foreign_country.foreign_countries;
                edit_employee_foreign_country.employee_id = employee_foreign_country.employee_id;
                edit_employee_foreign_country.out_date = employee_foreign_country.out_date;
                edit_employee_foreign_country.in_date = employee_foreign_country.in_date;
                edit_employee_foreign_country.country = employee_foreign_country.country;
                edit_employee_foreign_country.reason = employee_foreign_country.reason;
                edit_employee_foreign_country.number_decision = employee_foreign_country.number_decision;
                edit_employee_foreign_country.descriptions = employee_foreign_country.descriptions;
                data.SaveChanges();
            }
        }

        public int createEmployeeForeignCountries(employee_foreign_countries employee_foreign_country)
        {
            using (var data = new baseEntities())
            {
                data.AddToemployee_foreign_countries(employee_foreign_country);
                data.SaveChanges();

                return employee_foreign_country.foreign_countries;
            }
        }
        ///
        /// <summary>
        /// employee_school
        /// </summary>
        /// <param name="pageindex"></param>
        /// <param name="pagesite"></param>
        /// <param name="text_search"></param>
        /// <returns></returns>
        public PagedList<employee_school> getemployee_schoolBuilder(int pageindex, int pagesite, int? employeeId, string text_search)
        {
            using (var data = new baseEntities())
            {

                var list_employee_school = (from p in data.employee_school
                                            select p);

                if (!string.IsNullOrEmpty(text_search))
                {
                    list_employee_school = list_employee_school.Where(p => p.courses.Contains(text_search));
                }

                list_employee_school = list_employee_school.OrderBy(p => p.employee_school_id);

                return list_employee_school.ToPagedList(pageindex, pagesite);
            }
        }

        public employee_school getEmployeeSchoolByID(int id)
        {
            using (var data = new baseEntities())
            {
                var employee_school = data.employee_school.Where(p => p.employee_school_id == id).FirstOrDefault();

                return employee_school;
            }
        }

        public void editEmployeeSchool(employee_school employee_school)
        {
            using (var data = new baseEntities())
            {
                var edit_employee_school = data.employee_school.Where(p => p.employee_school_id == employee_school.employee_school_id).SingleOrDefault();
                edit_employee_school.employee_school_id = employee_school.employee_school_id;
                edit_employee_school.employee_id = employee_school.employee_id;
                edit_employee_school.school_id = employee_school.school_id;
                edit_employee_school.courses = employee_school.courses;
                edit_employee_school.majors = employee_school.majors;
                edit_employee_school.study_level = employee_school.study_level;
                edit_employee_school.start_date = employee_school.start_date;
                edit_employee_school.start_date_d = employee_school.start_date_d;
                edit_employee_school.start_date_m = employee_school.start_date_m;
                edit_employee_school.start_date_y = employee_school.start_date_y;
                edit_employee_school.end_date = employee_school.end_date;
                edit_employee_school.end_date_d = employee_school.end_date_d;
                edit_employee_school.end_date_m = employee_school.end_date_m;
                edit_employee_school.end_date_y = employee_school.end_date_y;
                edit_employee_school.diplomas = employee_school.diplomas;
                edit_employee_school.descriptions = employee_school.descriptions;
                edit_employee_school.created_date = employee_school.created_date;
                edit_employee_school.created_by = employee_school.created_by;
                edit_employee_school.modified_date = employee_school.modified_date;
                edit_employee_school.modified_by = employee_school.modified_by;
                data.SaveChanges();
            }
        }

        public int createEmployeeSchool(employee_school employee_school)
        {
            using (var data = new baseEntities())
            {
                data.AddToemployee_school(employee_school);
                data.SaveChanges();

                return employee_school.employee_school_id;
            }
        }
        public List<school> getListSchool()
        {
            using (var data = new baseEntities())
            {
                var list_school = data.schools.OrderBy(p => p.school_id).ToList();
                return list_school;
            }
        }
        public List<school> listschool()
        {
            using (var data = new baseEntities())
            {
                var branch_ = data.schools.ToList();
                return branch_;
            }
        }
        //

        ///
        /// <summary>
        /// tbl_leave_manage
        /// </summary>
        /// <param name="pageindex"></param>
        /// <param name="pagesite"></param>
        /// <param name="text_search"></param>
        /// <returns></returns>
        public PagedList<leave_manage> getleave_manageBuilder(int pageindex, int pagesite, int? employee_id, string text_search)
        {
            using (var data = new baseEntities())
            {

                var list_leave_manage = (from p in data.leave_manage
                                         select p);
                if (employee_id != null) { list_leave_manage = list_leave_manage.Where(p => p.employee_id == employee_id); }
                if (!string.IsNullOrEmpty(text_search))
                {
                    list_leave_manage = list_leave_manage.Where(p => p.descriptions.Contains(text_search));
                }

                list_leave_manage = list_leave_manage.OrderBy(p => p.leave_id);

                return list_leave_manage.ToPagedList(pageindex, pagesite);
            }
        }

        public leave_manage getLeaveManageByID(int id)
        {
            using (var data = new baseEntities())
            {
                var leave_manage = data.leave_manage.Where(p => p.leave_id == id).FirstOrDefault();

                return leave_manage;
            }
        }

        public void editLeaveManage(leave_manage leave_manage)
        {
            using (var data = new baseEntities())
            {
                var edit_leave_manage = data.leave_manage.Where(p => p.leave_id == leave_manage.leave_id).SingleOrDefault();
                edit_leave_manage.leave_id = leave_manage.leave_id;
                edit_leave_manage.type_leave_id = leave_manage.type_leave_id;
                edit_leave_manage.employee_id = leave_manage.employee_id;
                edit_leave_manage.start_leave = leave_manage.start_leave;
                edit_leave_manage.start_leave_d = leave_manage.start_leave_d;
                edit_leave_manage.start_leave_m = leave_manage.start_leave_m;
                edit_leave_manage.start_leave_y = leave_manage.start_leave_y;
                edit_leave_manage.end_leave = leave_manage.end_leave;
                edit_leave_manage.end_leave_d = leave_manage.end_leave_d;
                edit_leave_manage.end_leave_m = leave_manage.end_leave_m;
                edit_leave_manage.end_leave_y = leave_manage.end_leave_y;
                edit_leave_manage.descriptions = leave_manage.descriptions;
                data.SaveChanges();
            }
        }

        public int createLeaveManage(leave_manage leave_manage)
        {
            using (var data = new baseEntities())
            {
                data.AddToleave_manage(leave_manage);
                data.SaveChanges();

                return leave_manage.leave_id;
            }
        }
        public List<type_leave> getListtype_leave()
        {
            using (var data = new baseEntities())
            {
                var list_type_leave = data.type_leave.OrderBy(p => p.type_leave_id).ToList();
                return list_type_leave;
            }
        }
        public List<type_leave> listtype_leave()
        {
            using (var data = new baseEntities())
            {
                var branch_ = data.type_leave.ToList();
                return branch_;
            }
        }

        /// <summary>
        /// employee_visa_passport
        /// </summary>
        /// <param name="pageindex"></param>
        /// <param name="pagesite"></param>
        /// <param name="text_search"></param>
        /// <returns></returns>
        public PagedList<employee_visa_passport> getemployee_visa_passportBuilder(int pageindex, int pagesite, int? employeeId, string text_search)
        {
            using (var data = new baseEntities())
            {

                var list_employee_visa_passport = (from p in data.employee_visa_passport
                                                   select p);

                if (!string.IsNullOrEmpty(text_search))
                {
                    list_employee_visa_passport = list_employee_visa_passport.Where(p => p.passport_code.Contains(text_search));
                }

                list_employee_visa_passport = list_employee_visa_passport.OrderBy(p => p.passport_id);

                return list_employee_visa_passport.ToPagedList(pageindex, pagesite);
            }
        }
        public employee_visa_passport getEmployeeVisaPassportByID(int id)
        {
            using (var data = new baseEntities())
            {
                var employee_visa_passport = data.employee_visa_passport.Where(p => p.passport_id == id).FirstOrDefault();

                return employee_visa_passport;
            }
        }

        public void editEmployeeVisaPassport(employee_visa_passport employee_visa_passport)
        {
            using (var data = new baseEntities())
            {
                var edit_employee_visa_passport = data.employee_visa_passport.Where(p => p.passport_id == employee_visa_passport.passport_id).SingleOrDefault();
                edit_employee_visa_passport.passport_id = employee_visa_passport.passport_id;
                edit_employee_visa_passport.employee_id = employee_visa_passport.employee_id;
                edit_employee_visa_passport.passport_code = employee_visa_passport.passport_code;
                edit_employee_visa_passport.passport_number = employee_visa_passport.passport_number;
                edit_employee_visa_passport.passport_issuing_date = employee_visa_passport.passport_issuing_date;
                edit_employee_visa_passport.passport_issuing_date_d = employee_visa_passport.passport_issuing_date_d;
                edit_employee_visa_passport.passport_issuing_date_m = employee_visa_passport.passport_issuing_date_m;
                edit_employee_visa_passport.passport_issuing_date_y = employee_visa_passport.passport_issuing_date_y;

                edit_employee_visa_passport.enable_start = employee_visa_passport.enable_start;
                edit_employee_visa_passport.enable_start_d = employee_visa_passport.enable_start_d;
                edit_employee_visa_passport.enable_start_m = employee_visa_passport.enable_start_m;
                edit_employee_visa_passport.enable_start_y = employee_visa_passport.enable_start_y;

                edit_employee_visa_passport.enable_end = employee_visa_passport.enable_end;
                edit_employee_visa_passport.enable_end_d = employee_visa_passport.enable_end_d;
                edit_employee_visa_passport.enable_end_m = employee_visa_passport.enable_end_m;
                edit_employee_visa_passport.enable_end_y = employee_visa_passport.enable_end_y;
                edit_employee_visa_passport.place_of_issue = employee_visa_passport.place_of_issue;
                edit_employee_visa_passport.visa_passport_status = employee_visa_passport.visa_passport_status;
                data.SaveChanges();
            }
        }

        public int createEmployeeVisaPassport(employee_visa_passport employee_visa_passport)
        {
            using (var data = new baseEntities())
            {
                data.AddToemployee_visa_passport(employee_visa_passport);
                data.SaveChanges();

                return employee_visa_passport.passport_id;
            }
        }
        //Ket thuc content_service thuy lam 
        public PagedList<type_contract> getPagerTypeContract(int pageindex, int pagesite, string text_search)
        {
            using (var data = new baseEntities())
            {

                var list_type_contract = (from p in data.type_contract
                                        select p);
               
                if (!string.IsNullOrEmpty(text_search))
                {
                    list_type_contract = list_type_contract.Where(p => p.type_contract_name.Contains(text_search));
                }

                list_type_contract = list_type_contract.OrderBy(p => p.type_contract_id);

                return list_type_contract.ToPagedList(pageindex, pagesite);
            }
        }

        public type_contract getTypeContractByID(int id)
        {
            using (var data = new baseEntities())
            {
                var contract = data.type_contract.Where(p => p.type_contract_id == id).FirstOrDefault();

                return contract;
            }
        }

        public void editTypeContract(type_contract type_contract)
        {
            using (var data = new baseEntities())
            {
                var edit_type_contract = data.type_contract.Where(p => p.type_contract_id == type_contract.type_contract_id).SingleOrDefault();

                edit_type_contract.type_contract_name = type_contract.type_contract_name;
                edit_type_contract.descriptions = type_contract.descriptions;
                data.SaveChanges();
            }
        }

        public int createTypeContract(type_contract type_contract)
        {
            using (var data = new baseEntities())
            {
                data.AddTotype_contract(type_contract);
                data.SaveChanges();

                return type_contract.type_contract_id;
            }
        }

        //các loại hình nghỉ
        public PagedList<type_leave> getPagerTypeLeave(int pageindex, int pagesite, string text_search)
        {
            using (var data = new baseEntities())
            {

                var list_type_leave = (from p in data.type_leave
                                       select p);

                if (!string.IsNullOrEmpty(text_search))
                {
                    list_type_leave = list_type_leave.Where(p => p.type_leave_name.Contains(text_search));
                }

                list_type_leave = list_type_leave.OrderBy(p => p.type_leave_id);

                return list_type_leave.ToPagedList(pageindex, pagesite);
            }
        }

        public type_leave getTypeLeaveByID(int id)
        {
            using (var data = new baseEntities())
            {
                var leave = data.type_leave.Where(p => p.type_leave_id == id).FirstOrDefault();

                return leave;
            }
        }
       
        public void editTypeLeave(type_leave type_leave)
        {
            using (var data = new baseEntities())
            {
                var edit_type_leave = data.type_leave.Where(p => p.type_leave_id == type_leave.type_leave_id).SingleOrDefault();

                edit_type_leave.type_leave_name = type_leave.type_leave_name;
                edit_type_leave.descriptions = type_leave.descriptions;
                data.SaveChanges();
            }
        }

        public int createTypeLeave(type_leave type_leave)
        {
            using (var data = new baseEntities())
            {
                data.AddTotype_leave(type_leave);
                data.SaveChanges();

                return type_leave.type_leave_id;
            }
        }

        //Tạo thân nhân
        public int createEmployeeFamily(employee_family employee_family)
        {
            using (var data = new baseEntities())
            {
                data.AddToemployee_family(employee_family);
                data.SaveChanges();

                return employee_family.family_id;
            }
        }
        //sửa thân nhân
        public employee_family getEmployeeFamilyByID(int id)
        {
            using (var data = new baseEntities())
            {
                var employee_family = data.employee_family.Where(p => p.family_id == id).FirstOrDefault();
                return employee_family;
            }
        }

        public void editEmployeeFamily(employee_family employee_family)
        {
            using (var data = new baseEntities())
            {
                var edit_employee_family = data.employee_family.Where(p => p.family_id == employee_family.family_id).SingleOrDefault();
                edit_employee_family.employee_id = employee_family.employee_id;
                edit_employee_family.family_id = employee_family.family_id;
                edit_employee_family.files = employee_family.files;
                edit_employee_family.fullname = employee_family.fullname;
                edit_employee_family.hometown = employee_family.hometown;
                edit_employee_family.current_address = employee_family.current_address;
                edit_employee_family.birth_day = employee_family.birth_day;
                edit_employee_family.job = employee_family.job;
                edit_employee_family.obj_family = employee_family.obj_family;
                edit_employee_family.family_status = employee_family.family_status;
                edit_employee_family.created_date = employee_family.created_date;
                edit_employee_family.modified_date = employee_family.modified_date;
                edit_employee_family.created_by = employee_family.created_by;
                edit_employee_family.modified_by = employee_family.modified_by;
                data.SaveChanges();
            }
        }

     

        //Tạo hồ sơ bảo hiểm
        public int createEmployeeInsurance(employee_insurance employee_insurance)
        {
            using (var data = new baseEntities())
            {
                data.AddToemployee_insurance(employee_insurance);
                data.SaveChanges();

                return employee_insurance.insurance_id;
            }
        }
        //sửa hồ sơ bảo hiểm
        public employee_insurance getEmployeeInsuranceByID(int id)
        {
            using (var data = new baseEntities())
            {
                var employee_insurance = data.employee_insurance.Where(p => p.insurance_id == id).FirstOrDefault();

                return employee_insurance;
            }
        }

        public void editEmployeeInsurance(employee_insurance employee_insurance)
        {
            using (var data = new baseEntities())
            {
                var edit_employee_insurance = data.employee_insurance.Where(p => p.insurance_id == employee_insurance.insurance_id).SingleOrDefault();
                edit_employee_insurance.employee_id = employee_insurance.employee_id;
                edit_employee_insurance.insurance_id = employee_insurance.insurance_id;
                edit_employee_insurance.start_date = employee_insurance.start_date;
                edit_employee_insurance.start_date_d = employee_insurance.start_date_d;
                edit_employee_insurance.start_date_m = employee_insurance.start_date_m;
                edit_employee_insurance.start_date_y = employee_insurance.start_date_y;
                edit_employee_insurance.content = employee_insurance.content;
                edit_employee_insurance.premium = employee_insurance.premium;
                edit_employee_insurance.number_insurance_book = employee_insurance.number_insurance_book;
                edit_employee_insurance.insurance_status = employee_insurance.insurance_status;
                edit_employee_insurance.created_date = employee_insurance.created_date;
                edit_employee_insurance.modified_date = employee_insurance.modified_date;
                edit_employee_insurance.created_by = employee_insurance.created_by;
                edit_employee_insurance.modified_by = employee_insurance.modified_by;
                data.SaveChanges();
            }
        }
        //Tạo hồ sơ luân chuyển --> Chuyển thành " Tạo hồ sơ quá trình công tác"
        public int createEmployeeRotation(employee_rotation employee_rotation)
        {
            using (var data = new baseEntities())
            {
                data.AddToemployee_rotation(employee_rotation);
                data.SaveChanges();

                return employee_rotation.rotation_id;
            }
        }
        //Cập nhật hồ sơ luân chuyển --> Chuyển thành " Cập nhật hồ sơ quá trình công tác"
        public employee_rotation getEmployeeRotationByID(int id)
        {
            using (var data = new baseEntities())
            {
                var employee_rotation = data.employee_rotation.Where(p => p.rotation_id == id).FirstOrDefault();

                return employee_rotation;
            }
        }

        public void editEmployeeRotation(employee_rotation employee_rotation)
        {
            using (var data = new baseEntities())
            {
                var edit_employee_rotation = data.employee_rotation.Where(p => p.rotation_id == employee_rotation.rotation_id).SingleOrDefault();
                edit_employee_rotation.employee_id = employee_rotation.employee_id;
                edit_employee_rotation.rotation_id = employee_rotation.rotation_id;
                edit_employee_rotation.branch = employee_rotation.branch;
                edit_employee_rotation.departments_id = employee_rotation.departments_id;
                edit_employee_rotation.levels = employee_rotation.levels;
                edit_employee_rotation.positions = employee_rotation.positions;
                edit_employee_rotation.unit = employee_rotation.unit;
                edit_employee_rotation.from_date = employee_rotation.from_date;
                edit_employee_rotation.from_date_d = employee_rotation.from_date_d;
                edit_employee_rotation.from_date_m = employee_rotation.from_date_m;
                edit_employee_rotation.from_date_y = employee_rotation.from_date_y;
                edit_employee_rotation.to_date = employee_rotation.to_date;
                edit_employee_rotation.to_date_d = employee_rotation.to_date_d;
                edit_employee_rotation.to_date_m = employee_rotation.to_date_m;
                edit_employee_rotation.to_date_y = employee_rotation.to_date_y;

                edit_employee_rotation.rotation_status = employee_rotation.rotation_status;
                edit_employee_rotation.created_date = employee_rotation.created_date;
                edit_employee_rotation.modified_date = employee_rotation.modified_date;
                edit_employee_rotation.created_by = employee_rotation.created_by;
                edit_employee_rotation.modified_by = employee_rotation.modified_by;
                data.SaveChanges();
            }
        }


        //Tạo hồ sơ 
        public int createEmployee(employee employee)
        {
            using (var data = new baseEntities())
            {
                data.AddToemployees(employee);
                data.SaveChanges();

                return employee.employee_id;
            }
        }
        //cập nhật hồ sơ 
        public employee getEmployeeByID(int id)
        {
            using (var data = new baseEntities())
            {
                var employee = data.employees.Where(p => p.employee_id == id).FirstOrDefault();

                return employee;
            }
        }

        public void editEmployee(employee employee)
        {
            using (var data = new baseEntities())
            {
                var edit_employee = data.employees.Where(p => p.employee_id == employee.employee_id).SingleOrDefault();
                edit_employee.employee_id = employee.employee_id;
                edit_employee.employee_code = employee.employee_code ;
                edit_employee.full_name = employee.full_name ;
                edit_employee.birth_day = employee.birth_day ;
                edit_employee.b_day = employee.b_day;
                edit_employee.b_month = employee.b_month;
                edit_employee.b_year = employee.b_year;

                edit_employee.phone = employee.phone ;
                edit_employee.mobile = employee.mobile;
                edit_employee.email = employee.email ;
                edit_employee.gender = employee.gender ;
                edit_employee.hometown = employee.hometown ;
                edit_employee.current_address = employee.current_address ;
                edit_employee.household_registration = employee.household_registration ;
                edit_employee.dateIn_doan_d = employee.dateIn_doan_d;
                edit_employee.dateIn_doan_m = employee.dateIn_doan_m;
                edit_employee.dateIn_doan_y = employee.dateIn_doan_y;
                edit_employee.dateIn_doan = employee.dateIn_doan ;

                edit_employee.dateIn_dang = employee.dateIn_dang ;
                edit_employee.dateIn_dang_d = employee.dateIn_dang_d;
                edit_employee.dateIn_dang_m = employee.dateIn_dang_m;
                edit_employee.dateIn_dang_y = employee.dateIn_dang_y;
                

                edit_employee.ranks = employee.ranks ;
                edit_employee.salary_coefficient = employee.salary_coefficient ;
                edit_employee.positions = employee.positions ;
                edit_employee.marital_status = employee.marital_status ;
                edit_employee.serial_number_officer = employee.serial_number_officer ;
                edit_employee.serial_number_military = employee.serial_number_military ;
                edit_employee.identity_card = employee.identity_card ;
                edit_employee.issuing_date = employee.issuing_date ;
                edit_employee.issuing_date_d = employee.issuing_date_d;
                edit_employee.issuing_date_m = employee.issuing_date_m;
                edit_employee.issuing_date_y = employee.issuing_date_y;

                edit_employee.place_of_issue = employee.place_of_issue ;
                edit_employee.army_identity_card = employee.army_identity_card ;
                edit_employee.army_issuing_date = employee.army_issuing_date ;
                edit_employee.army_issuing_date_d = employee.army_issuing_date_d;
                edit_employee.army_issuing_date_m = employee.army_issuing_date_m;
                edit_employee.army_issuing_date_y = employee.army_issuing_date_y;

                edit_employee.army_place_of_issue = employee.army_place_of_issue ;
                edit_employee.cultural_level = employee.cultural_level ;
                edit_employee.alias = employee.alias;
                edit_employee.bank_account = employee.bank_account;
                edit_employee.bank_name = employee.bank_name;
                edit_employee.tax_code = employee.tax_code;
                edit_employee.family_reduction = employee.family_reduction;
                edit_employee.house_land_status = employee.house_land_status;
                edit_employee.ethnic = employee.ethnic;
                edit_employee.religion = employee.religion;
                edit_employee.army_join = employee.army_join;
                edit_employee.army_join_d = employee.army_join_d;
                edit_employee.army_join_m = employee.army_join_m;
                edit_employee.army_join_y = employee.army_join_y;

                edit_employee.employee_status = employee.employee_status ;
                edit_employee.foreign_language = employee.foreign_language;
                edit_employee.object_employee_id = employee.object_employee_id ;
                //edit_employee.departments_id = employee.departments_id ;
                edit_employee.avarta = employee.avarta;
                edit_employee.description_detail = employee.description_detail;
                edit_employee.army_issuing_date = employee.army_issuing_date;
                edit_employee.army_issuing_date_d = employee.army_issuing_date_d;
                edit_employee.army_issuing_date_m = employee.army_issuing_date_m;
                edit_employee.army_issuing_date_y = employee.army_issuing_date_y;
                edit_employee.date_join = employee.date_join;
                edit_employee.date_join_d = employee.date_join_d;
                edit_employee.date_join_m = employee.date_join_m;
                edit_employee.date_join_y = employee.date_join_y;

                //edit_employee.unit = employee.unit;
                edit_employee.created_date = employee.created_date;
                edit_employee.modified_date = employee.modified_date;
                edit_employee.created_by = employee.created_by;
                edit_employee.modified_by = employee.modified_by;

                data.SaveChanges();
            }
        }

        public List<departments> getListDepartment()
        {
            using (var data = new baseEntities())
            {
                var list = data.departments.Where(p => p.departments_status == 1).ToList();

                return list;
            }
        }

        public List<object_employee> getListObjEmployee()
        {
            using (var data = new baseEntities())
            {
                var list = data.object_employee.ToList();

                return list;
            }
        }


        //Tạo hợp đồng
        public int createContract(contract contract)
        {
            using (var data = new baseEntities())
            {
                data.AddTocontracts(contract);
                data.SaveChanges();

                return contract.contract_id ;
            }
        }
        public List<type_contract> getListTypeContract()
        {
            using (var data = new baseEntities())
            {
                var list = data.type_contract.ToList();

                return list;
            }
        }
        //cập nhật hợp đồng
        public contract getContractByID(int id)
        {
            using (var data = new baseEntities())
            {
                var contract = data.contracts.Where(p => p.contract_id == id).FirstOrDefault();

                return contract;
            }
        }
        public void edit_employee_contract(contract contract)
        {
            using (var data = new baseEntities())
            {
                var edit_contract = data.contracts.Where(p => p.contract_id == contract.contract_id).SingleOrDefault();

                edit_contract.contract_id = contract.contract_id;
                edit_contract.contract_name = contract.contract_name;

                edit_contract.enable_start = contract.enable_start;
                edit_contract.enable_start_d = contract.enable_start_d;
                edit_contract.enable_start_m = contract.enable_start_m;
                edit_contract.enable_start_y = contract.enable_start_y;

                edit_contract.enable_end_d = contract.enable_end_d;
                edit_contract.enable_end_m = contract.enable_end_m;
                edit_contract.enable_end_y = contract.enable_end_y;
                edit_contract.enable_end = contract.enable_end;

                edit_contract.contract_status = contract.contract_status;
                edit_contract.files = contract.files;
                edit_contract.signature_name = contract.signature_name;

                edit_contract.signature_date = contract.signature_date;
                edit_contract.signature_date_d = contract.signature_date_d;
                edit_contract.signature_date_m = contract.signature_date_m;
                edit_contract.signature_date_y = contract.signature_date_y;
                edit_contract.positions = contract.positions;
                edit_contract.type_contract_id = contract.type_contract_id;
                edit_contract.employee_id = contract.employee_id;
                data.SaveChanges();
            }
        }

        //Tạo hồ sơ sức khỏe
        public int createMedicalRecord(employee_medical_records medicalRecord)
        {
            using (var data = new baseEntities())
            {
                data.AddToemployee_medical_records(medicalRecord);
                data.SaveChanges();

                return medicalRecord.medical_records_id;
            }
        }
        //cập nhật hồ sơ sức khỏe
        public employee_medical_records getMedicalRecordByID(int id)
        {
            using (var data = new baseEntities())
            {
                var medicalRecord = data.employee_medical_records.Where(p => p.medical_records_id == id).FirstOrDefault();

                return medicalRecord;
            }
        }
        public void edit_employee_medical_record(employee_medical_records medicalRecord)
        {
            using (var data = new baseEntities())
            {
                var edit_medicalRecord = data.employee_medical_records.Where(p => p.medical_records_id == medicalRecord.medical_records_id).SingleOrDefault();
                edit_medicalRecord.medical_records_id = medicalRecord.medical_records_id;
                edit_medicalRecord.employee_id = medicalRecord.employee_id;
                edit_medicalRecord.date_medical_examination = medicalRecord.date_medical_examination;
                edit_medicalRecord.date_medical_examination_d = medicalRecord.date_medical_examination_d;
                edit_medicalRecord.date_medical_examination_m = medicalRecord.date_medical_examination_m;
                edit_medicalRecord.date_medical_examination_y = medicalRecord.date_medical_examination_y;
                edit_medicalRecord.medical_status = medicalRecord.medical_status;
                edit_medicalRecord.weights = medicalRecord.weights;
                edit_medicalRecord.height = medicalRecord.height;
                edit_medicalRecord.hospital = medicalRecord.hospital;
                edit_medicalRecord.address_examination = medicalRecord.address_examination;
                edit_medicalRecord.blood_group = medicalRecord.blood_group;
                edit_medicalRecord.major_disease = medicalRecord.major_disease;
                edit_medicalRecord.descriptions = medicalRecord.descriptions;
                edit_medicalRecord.created_date = medicalRecord.created_date;
                edit_medicalRecord.created_by = medicalRecord.created_by;
                edit_medicalRecord.modified_date = medicalRecord.modified_date;
                edit_medicalRecord.modified_by = medicalRecord.modified_by;     
                data.SaveChanges();
            }
        }

        //Tạo hồ sơ nghỉ việc
        public int createJobSeverance(employee_job_severance jobSeverance)
        {
            using (var data = new baseEntities())
            {
                data.AddToemployee_job_severance(jobSeverance);
                data.SaveChanges();

                return jobSeverance.job_severance_id;
            }
        }
        //cập nhật hồ sơ nghỉ việc
        public employee_job_severance getJobSeveranceByID(int id)
        {
            using (var data = new baseEntities())
            {
                var jobSeverance = data.employee_job_severance.Where(p => p.job_severance_id == id).FirstOrDefault();

                return jobSeverance;
            }
        }
        public void edit_employee_jobSeverance(employee_job_severance jobSeverance)
        {
            using (var data = new baseEntities())
            {
                var edit_medicalRecord = data.employee_job_severance.Where(p => p.job_severance_id == jobSeverance.job_severance_id).SingleOrDefault();
                edit_medicalRecord.job_severance_id = jobSeverance.job_severance_id;
                edit_medicalRecord.employee_id = jobSeverance.employee_id;
                edit_medicalRecord.form_job_severance = jobSeverance.form_job_severance;
                edit_medicalRecord.date_job_severance = jobSeverance.date_job_severance;
                edit_medicalRecord.date_job_severance_d = jobSeverance.date_job_severance_d;
                edit_medicalRecord.date_job_severance_m = jobSeverance.date_job_severance_m;
                edit_medicalRecord.date_job_severance_y = jobSeverance.date_job_severance_y;
                edit_medicalRecord.subsidy = jobSeverance.subsidy;
                edit_medicalRecord.refund_fee_education = jobSeverance.refund_fee_education;
                edit_medicalRecord.compensation = jobSeverance.compensation;
                edit_medicalRecord.created_date = jobSeverance.created_date;
                edit_medicalRecord.created_by = jobSeverance.created_by;
                edit_medicalRecord.modified_date = jobSeverance.modified_date;
                edit_medicalRecord.modified_by = jobSeverance.modified_by;
                data.SaveChanges();
            }
        }

        //Danh sach khen thuong
        /*public PagedList<reward> getPagerReward(int pageindex, int pagesite, string text_search)
        {
            using (var data = new baseEntities())
            {

                var list_reward = (from p in data.rewards
                                   select p);

                if (!string.IsNullOrEmpty(text_search))
                {
                    list_reward = list_reward.Where(p => p.reward_name.Contains(text_search));
                }

                list_reward = list_reward.OrderBy(p => p.reward_id);

                return list_reward.ToPagedList(pageindex, pagesite);
            }
        }
*/
        public reward getRewardByID(int id)
        {
            using (var data = new baseEntities())
            {
                var contract = data.rewards.Where(p => p.reward_id == id).FirstOrDefault();

                return contract;
            }
        }
        public void editReward(reward reward)
        {
            using (var data = new baseEntities())
            {
                var edit_reward = data.rewards.Where(p => p.reward_id == reward.reward_id).SingleOrDefault();
                edit_reward.reward_id = reward.reward_id;
                edit_reward.employee_id = reward.employee_id;
                edit_reward.departments_id = reward.departments_id;
                edit_reward.reward_number = reward.reward_number;
                edit_reward.reward_name = reward.reward_name;
                edit_reward.signature_name = reward.signature_name;
                edit_reward.signature_date = reward.signature_date;
                edit_reward.descriptions = reward.descriptions;
                edit_reward.is_collective = reward.is_collective;
                edit_reward.reward_name_group = reward.reward_name_group;
                edit_reward.reward_status = reward.reward_status;
                edit_reward.form_reward = reward.form_reward;
                edit_reward.skill_reward = reward.skill_reward;
                data.SaveChanges();
            }
        }

        public int createReward(reward reward)
        {
            using (var data = new baseEntities())
            {
                data.AddTorewards(reward);
                data.SaveChanges();

                return reward.reward_id;
            }
        }

        //Danh sach ky luat
        public PagedList<reward> getPagerDiscripline(int pageindex, int pagesite, string text_search,DateTime? from_date, DateTime? to_date)
        {
            using (var data = new baseEntities())
            {

                var list_discripline = (from p in data.rewards
                                        where p.type_reward == 3
                                        select p);

                if (!string.IsNullOrEmpty(text_search))
                {
                    list_discripline = list_discripline.Where(p => (p.reward_name.Contains(text_search)) || (p.descriptions.Contains(text_search)));
                }

                if (from_date == null)
                {
                    from_date = DateTime.Now.AddMonths(-6);
                }

                if (to_date == null)
                {
                    to_date = DateTime.Now;
                }

                if (from_date != null && to_date != null)
                {
                    list_discripline = list_discripline.Where(p => p.signature_date >= from_date && p.signature_date <= to_date);
                }

                list_discripline = list_discripline.OrderBy(p => p.reward_id);

                return list_discripline.ToPagedList(pageindex, pagesite);
            }
        }
        public List<reward> getPagerDiscriplineExport(string text_search, int? years,DateTime? from_date, DateTime? to_date)
        {
            using (var data = new baseEntities())
            {

                var list_discripline = (from p in data.rewards
                                   where p.type_reward == 3
                                   select p);

                if (!string.IsNullOrEmpty(text_search))
                {
                    list_discripline = list_discripline.Where(p => (p.reward_name.Contains(text_search)) || (p.reward_number.Contains(text_search)) || (p.signature_name.Contains(text_search)) || (p.medal_class.Contains(text_search)) || (p.type_medals.Contains(text_search)) || (p.descriptions.Contains(text_search)));
                }
                if (from_date == null)
                {
                    from_date = DateTime.Now.AddMonths(-6);
                }

                if (to_date == null)
                {
                    to_date = DateTime.Now;
                }

                if (from_date != null && to_date != null)
                {
                    list_discripline = list_discripline.Where(p => p.signature_date >= from_date && p.signature_date <= to_date);
                }
                if (years != null && years != 0)
                {
                    list_discripline = list_discripline.Where(p => p.Years == years);
                }

                list_discripline = list_discripline.OrderBy(p => p.reward_id);

                return list_discripline.ToList();
            }
        }
        public reward getDiscriplinedByID(int id)
        {
            using (var data = new baseEntities())
            {
                var discripline = data.rewards.Where(p => p.reward_id == id).FirstOrDefault();

                return discripline;
            }
        }

        //Tạo danh sách kỷ luật
        public int createDiscripline(reward discripline)
        {
            using (var data = new baseEntities())
            {
                data.AddTorewards(discripline);
                data.SaveChanges();

                return discripline.reward_id;
            }
        }
        //Cập nhật danh sách kỷ luật
        public void edit_Discripline(reward discripline)
        {
            using (var data = new baseEntities())
            {
                var edit_discripline = data.rewards.Where(p => p.reward_id == discripline.reward_id).SingleOrDefault();
                edit_discripline.reward_id = discripline.reward_id;
                edit_discripline.employee_id = discripline.employee_id;
                edit_discripline.departments_id = discripline.departments_id;
                edit_discripline.reward_number = discripline.reward_number;
                edit_discripline.reward_name = discripline.reward_name;
                edit_discripline.signature_name = discripline.signature_name;
                edit_discripline.signature_date = discripline.signature_date;
                edit_discripline.descriptions = discripline.descriptions;
                edit_discripline.is_collective = discripline.is_collective;
                edit_discripline.reward_name_group = discripline.reward_name_group;
                edit_discripline.reward_status = discripline.reward_status;
                data.SaveChanges();
            }
        }

        //Danh sach Huân huy Chương

        public PagedList<reward> getPagerMedals(int pageindex, int pagesite, string text_search, int? years, DateTime? from_date, DateTime? to_date)
        {
            using (var data = new baseEntities())
            {

                var list_medals = (from p in data.rewards
                                   where p.type_reward == 2 && p.is_collective ==1
                                   select p);

                if (!string.IsNullOrEmpty(text_search))
                {
                    list_medals = list_medals.Where(p => (p.reward_name.Contains(text_search)) || (p.reward_number.Contains(text_search)) || (p.signature_name.Contains(text_search)) || (p.medal_class.Contains(text_search)) || (p.type_medals.Contains(text_search)) || (p.descriptions.Contains(text_search)));
                }
                if (from_date == null)
                {
                    from_date = DateTime.Now.AddMonths(-6);
                }

                if (to_date == null)
                {
                    to_date = DateTime.Now;
                }

                if (from_date != null && to_date != null)
                {
                    list_medals = list_medals.Where(p => p.signature_date >= from_date && p.signature_date <= to_date);
                }
                if (years != null && years!=0)
                {
                    list_medals = list_medals.Where(p => p.Years == years);
                }

                list_medals = list_medals.OrderBy(p => p.reward_id);

                return list_medals.ToPagedList(pageindex, pagesite);
            }
        }
        public List<int> getListYears()
        {
            using (var data = new baseEntities())
            {
                var listYears = from p in data.rewards
                                where p.Years != null && p.is_collective==1
                                select p.Years??0;

                return listYears.Distinct().ToList();
            }
        }
        public List<int> getListYearsGroup()
        {
            using (var data = new baseEntities())
            {
                var listYearsGroup = from p in data.rewards
                                where p.Years != null && p.is_collective == 2
                                select p.Years ?? 0;

                return listYearsGroup.Distinct().ToList();
            }
        }
        public List<reward> getPagerMedalsCNExport(string text_search, int? years, DateTime? from_date, DateTime? to_date)
        {
            using (var data = new baseEntities())
            {

                var list_medals_CN = (from p in data.rewards
                                   where p.type_reward == 2 && p.is_collective==1
                                   select p);

                if (!string.IsNullOrEmpty(text_search))
                {
                    list_medals_CN = list_medals_CN.Where(p => (p.reward_name.Contains(text_search)) || (p.reward_number.Contains(text_search)) || (p.signature_name.Contains(text_search)) || (p.medal_class.Contains(text_search)) || (p.type_medals.Contains(text_search)) || (p.descriptions.Contains(text_search)));
                }
                if (from_date == null)
                {
                    from_date = DateTime.Now.AddMonths(-6);
                }

                if (to_date == null)
                {
                    to_date = DateTime.Now;
                }

                if (from_date != null && to_date != null)
                {
                    list_medals_CN = list_medals_CN.Where(p => p.signature_date >= from_date && p.signature_date <= to_date);
                }
                if (years != null && years != 0)
                {
                    list_medals_CN = list_medals_CN.Where(p => p.Years == years);
                }

                list_medals_CN = list_medals_CN.OrderBy(p => p.reward_id);

                return list_medals_CN.ToList();
            }
        }
        public List<reward> getPagerMedalsTTExport(string text_search, int? years, DateTime? from_date, DateTime? to_date)
        {
            using (var data = new baseEntities())
            {

                var list_medals_TT = (from p in data.rewards
                                   where p.type_reward == 2 && p.is_collective==2
                                   select p);

                if (!string.IsNullOrEmpty(text_search))
                {
                    list_medals_TT = list_medals_TT.Where(p => (p.reward_name.Contains(text_search)) || (p.reward_number.Contains(text_search)) || (p.signature_name.Contains(text_search)) || (p.medal_class.Contains(text_search)) || (p.type_medals.Contains(text_search)) || (p.descriptions.Contains(text_search)));
                }
                if (from_date == null)
                {
                    from_date = DateTime.Now.AddMonths(-6);
                }

                if (to_date == null)
                {
                    to_date = DateTime.Now;
                }

                if (from_date != null && to_date != null)
                {
                    list_medals_TT = list_medals_TT.Where(p => p.signature_date >= from_date && p.signature_date <= to_date);
                }
                if (years != null && years != 0)
                {
                    list_medals_TT = list_medals_TT.Where(p => p.Years == years);
                }

                list_medals_TT = list_medals_TT.OrderBy(p => p.reward_id);

                return list_medals_TT.ToList();
            }
        }
        public reward getMedalsByID(int id)
        {
            using (var data = new baseEntities())
            {
                var medals = data.rewards.Where(p => p.reward_id == id).FirstOrDefault();

                return medals;
            }
        }
        //Danh sách tặng huân huy chương cho tập thể 
        public PagedList<reward> getPagerMedalsGroup(int pageindex, int pagesite, string text_search, int? years, DateTime? from_date, DateTime? to_date)
        {
            using (var data = new baseEntities())
            {

                var list_medals_group = (from p in data.rewards
                                         where p.type_reward == 2 && p.is_collective == 2
                                         select p);

                if (!string.IsNullOrEmpty(text_search))
                {
                    list_medals_group = list_medals_group.Where(p => (p.reward_name.Contains(text_search)) || (p.reward_number.Contains(text_search)) || (p.signature_name.Contains(text_search)) || (p.medal_class.Contains(text_search)) || (p.type_medals.Contains(text_search)) || (p.descriptions.Contains(text_search)));
                }
                if (from_date == null)
                {
                    from_date = DateTime.Now.AddMonths(-6);
                }

                if (to_date == null)
                {
                    to_date = DateTime.Now;
                }

                if (from_date != null && to_date != null)
                {
                    list_medals_group = list_medals_group.Where(p => p.signature_date >= from_date && p.signature_date <= to_date);
                }
                if (years != null && years != 0)
                {
                    list_medals_group = list_medals_group.Where(p => p.Years == years);
                }

                list_medals_group = list_medals_group.OrderBy(p => p.reward_id);

                return list_medals_group.ToPagedList(pageindex, pagesite);
            }
        }
        //Cập nhật huân huy chương
        public void edit_Medals(reward medals)
        {
            using (var data = new baseEntities())
            {
                var edit_medical = data.rewards.Where(p => p.reward_id == medals.reward_id).SingleOrDefault();
                edit_medical.reward_id=medals.reward_id;
                edit_medical.departments_id = medals.departments_id;
                edit_medical.employee_id = medals.employee_id;
                edit_medical.type_medals = medals.type_medals;
                edit_medical.medal_class = medals.medal_class;
                edit_medical.reward_number = medals.reward_number;
                edit_medical.signature_name = medals.signature_name;
                edit_medical.signature_date = medals.signature_date;
                edit_medical.descriptions = medals.descriptions;
                edit_medical.Years = medals.Years;
                //edit_medical.reward_number = medals.reward_number;
                edit_medical.reward_name_group = medals.reward_name_group;
                edit_medical.is_collective = medals.is_collective;
                data.SaveChanges();
            }
        }
        //Tạo huân huy chương
        public int createMedals(reward medals)
        {
            using (var data = new baseEntities())
            {
                data.AddTorewards(medals);
                data.SaveChanges();

                return medals.reward_id;
            }
        }
        public List<employee> getListEmployees()
        {
            using (var data = new baseEntities())
            {
                var list = data.employees.Where(p => p.employee_status == 1).ToList();
                return list;
            }
        }
        public List<employee> getListEmployeesBirthday()
        {

            using (var data = new baseEntities())
            {
                var list = data.employees.Where(p => p.employee_status == 1).ToList();
                //return list;
                List<employee> get_employee = new List<employee>();
                foreach (var item in list)
                {
                    item.full_name = item.full_name + ".........(" + String.Format("{0:dd/MM/yyyy}", item.birth_day) + ")";
                    get_employee.Add(item);
                }
                return get_employee;
            }        
            
        }


        public void deleteMedals(int reward_id)
        {
            using (var data = new baseEntities())
            {
                var reward = data.rewards.Where(p => p.reward_id == reward_id).FirstOrDefault();

                data.DeleteObject(reward);
                data.SaveChanges();
            }
        }

        //tạo loại huân huy chương

        public type_reward getTypRewardByID(int id)
        {
            using (var data = new baseEntities())
            {
                var type_reward = data.type_reward.Where(p => p.type_reward_id == id).FirstOrDefault();
                return type_reward;
            }
        }

        public List<type_reward> getListTypeRewardByParent(int? id)
        {
            using (var data = new baseEntities())
            {
                var list_type_reward = data.type_reward.Where(p => p.type_reward_name != null);
                if (id != null)
                {
                    list_type_reward = list_type_reward.Where(p => p.parent_id == id);
                }
                else
                {
                    list_type_reward = list_type_reward.Where(p => p.parent_id == null);
                }

                return list_type_reward.ToList();
            }
        }

        public PagedList<type_reward> getTypeRewdBuilder(int pageindex, int pagesite, string text_search, int? parent)
        {
            using (var data = new baseEntities())
            {
                IQueryable<type_reward> list = getListTypeRewardBuilder(parent).AsQueryable<type_reward>();

                if (!string.IsNullOrEmpty(text_search))
                {
                    list = list.Where(p => p.type_reward_name.Contains(text_search) || p.type_reward_desc.Contains(text_search));
                }

                list = list.OrderBy(p => p.type_reward_id);

                return list.ToPagedList(pageindex, pagesite);
            }
        }

        public List<type_reward> getListTypeRewardBuilder(int? id)
        {
            List<type_reward> sb = new List<type_reward>();
            return getListTypeRewardBuilder(ref sb, id, true);
        }

        public List<type_reward> getListTypeRewardBuilder(ref List<type_reward> sb, int? id, bool root)
        {
            List<type_reward> list_parent = getListTypeRewardByParent(id);
            foreach (var item in list_parent)
            {
                if (!root)
                {
                    item.type_reward_name = getTypRewardByID((int)item.parent_id).type_reward_name + ">>" + item.type_reward_name;
                    type_reward type_emp = getTypRewardByID(id ?? 0);
                    foreach (var itemStr in sb)
                    {
                        if (itemStr.type_reward_id == type_emp.type_reward_id)
                        {
                            if (itemStr.type_reward_name.Contains(">>"))
                            {
                                int laststr = itemStr.type_reward_name.LastIndexOf(">>") + 2;
                                string addStr = itemStr.type_reward_name.Substring(0, laststr);
                                item.type_reward_name = addStr + item.type_reward_name;
                            }
                        }
                    }
                }
                sb.Add(item);
                List<type_reward> list_child = getListTypeRewardByParent(item.type_reward_id);
                if (list_child.Count() > 0)
                {
                    getListTypeRewardBuilder(ref sb, item.type_reward_id, false);
                }
            }
            return sb;
        }
        public void editTypReward(type_reward type_reward)
        {
            using (var data = new baseEntities())
            {
                var edit_type_reward = data.type_reward.Where(p => p.type_reward_id == type_reward.type_reward_id).SingleOrDefault();
                edit_type_reward.type_reward_name = type_reward.type_reward_name;
                edit_type_reward.type_reward_desc = type_reward.type_reward_desc;
                edit_type_reward.parent_id = type_reward.parent_id;
                data.SaveChanges();
            }
        }
        public int createTypReward(type_reward type_reward)
        {
            using (var data = new baseEntities())
            {
                data.AddTotype_reward(type_reward);
                data.SaveChanges();

                return type_reward.type_reward_id;
            }
        }
        

        //danh sách phòng ban
        public departments getDepartmentsByID(int id)
        {
            using (var data = new baseEntities())
            {
                var departments = data.departments.Where(p => p.departments_id == id).FirstOrDefault();
                return departments;
            }
        }

        public List<departments> getListDepartmentsByParent(int? id)
        {
            using (var data = new baseEntities())
            {
                var list_departments = data.departments.Where(p => p.departments_name != null);
                if (id != null)
                {
                    list_departments = list_departments.Where(p => p.parent_departments == id);
                }
                else
                {
                    list_departments = list_departments.Where(p => p.parent_departments == null);
                }

                return list_departments.ToList();
            }
        }
        public List<departments> getListDepartmentsBuilder(ref List<departments> sb, int? id, bool root)
                {
                    List<departments> list_parent = getListDepartmentsByParent(id);
                    foreach (var item in list_parent)
                    {
                        if (!root)
                        {
                            item.departments_name = getDepartmentsByID((int)item.parent_departments).departments_name + ">>" + item.departments_name;
                            departments departments_emp = getDepartmentsByID(id ?? 0);
                            foreach (var itemStr in sb)
                            {
                                if (itemStr.departments_id == departments_emp.departments_id)
                                {
                                    if (itemStr.departments_name.Contains(">>"))
                                    {
                                        int laststr = itemStr.departments_name.LastIndexOf(">>") + 2;
                                        string addStr = itemStr.departments_name.Substring(0, laststr);
                                        item.departments_name = addStr + item.departments_name;
                                    }
                                }
                            }
                        }
                        sb.Add(item);
                        List<departments> list_child = getListDepartmentsByParent(item.departments_id);
                        if (list_child.Count() > 0)
                        {
                            getListDepartmentsBuilder(ref sb, item.departments_id, false);
                        }
                    }
                    return sb;
                }
        public List<departments> getListDepartmentsBuilder(int? id)
        {
            List<departments> sb = new List<departments>();
            return getListDepartmentsBuilder(ref sb, id, true);
        }
        
        public PagedList<departments> getDepartmentsBuilder(int pageindex, int pagesite, string text_search, int? parent)
        {
            using (var data = new baseEntities())
            {
                IQueryable<departments> list = getListDepartmentsBuilder(parent).AsQueryable<departments>();

                if (!string.IsNullOrEmpty(text_search))
                {
                    list = list.Where(p => p.departments_name.Contains(text_search) || p.descriptions.Contains(text_search));
                }

                list = list.OrderBy(p => p.departments_id);

                return list.ToPagedList(pageindex, pagesite);
            }
        }

        public int createDepartment(departments departments)
        {
            using (var data = new baseEntities())
            {
                data.AddTodepartments(departments);
                data.SaveChanges();

                return departments.departments_id;
            }
        }

        public void editDepartments(departments departments)
        {
            using (var data = new baseEntities())
            {
                var edit_departments = data.departments.Where(p => p.departments_id == departments.departments_id).SingleOrDefault();

                edit_departments.departments_name = departments.departments_name;
                edit_departments.departments_status = departments.departments_status;
                edit_departments.descriptions = departments.descriptions;
                edit_departments.parent_departments = departments.parent_departments;
                data.SaveChanges();
            }
        }

        public void addDepartmentEmployee(int departments_id, int employee_id)
        {
            using (var data = new baseEntities())
            {
                var check = data.department_employee.Where(p => p.departments_id == departments_id && p.employee_id == employee_id);
                if (check == null || check.Count() == 0)
                {
                    department_employee de= new department_employee();
                    de.departments_id = departments_id;
                    de.employee_id = employee_id;

                    data.AddTodepartment_employee(de);
                    data.SaveChanges();
                }
            }
        }
        public void deleteDepartmentEmployee(int employee_id)
        {
            using (var data = new baseEntities())
            {
                var list = data.department_employee.Where(p => p.employee_id == employee_id).ToList();
                foreach (var item in list)
                {
                    data.DeleteObject(item);
                    data.SaveChanges();
                }
            }
        }
        public List<int> getListDepartmentEmployee(int employee_id)
        {
            using (var data = new baseEntities())
            {
                var list_department_id = (from p in data.department_employee
                                     where p.employee_id == employee_id 
                                     select (int)p.departments_id).ToList();
                return list_department_id;
            }
        }
        
    }
}
